import pymysql
import pandas as pd
import pymysql.cursors

from confi import CITY_DICT

class MysqlUtils(object):
    """数据库工具类
    
    
    """
    
    def __init__(self):
        self.conn =pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='123456',
            database='sys',
            port=3306,
            charset='utf8'
        )
        
    def get_scensic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT LEFT(u.id_no, 4) as city_code, DATE_FORMAT(O.create_time, '%Y-%m') as month, count(u.id) as visitor_count 
        FROM  ticket_order_user_rel u JOIN ticket_order o on o.id = u.order_id WHERE LENGTH(u.id_no) = 18 and o.pay_time is not null 
        and o.pay_time != "" GROUP BY city_code, month
        """
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        new_lsit = []
        for item in ret:
            if item['city_code'] not in CITY_DICT:
                continue
            new_lsit.append({
                'city_code': item['city_code'],
                'month': item['month'],
                'visitor_count': item['visitor_count'],
                'city_name': CITY_DICT[item['city_code']]
            })
            #print(new_list)
        df_city_monthly = pd.DataFrame(new_lsit)
        df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'] + '-01')
            
        #假设当前月是2024-12
        current_month = pd.to_datetime('2024-12-01')
        def calculate_baseline(df, current_month, window_size=6):
            #提出当前月数据
            df_current = df[df['month'] == current_month]
            #提取历史数据
            history_start = current_month - pd.DateOffset(month=window_size)               
            df_history = df[(df['month'] > history_start) & (df['month'] < current_month)]    
            #按城市计算均值和标准差
            df_baseline =df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
            df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)   
            #合并当前月份数据
            df_merged = df_current.merge(df_baseline, on='city_name', how='left')
            return df_merged
        df_merged = calculate_baseline(df_city_monthly, current_month)
        #计算Z-score
        df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']
        #标记暴增暴跌的城市(z_score > 3 or z_score < -3)
        df_increased = df_merged[df_merged['z_score'] > 3]
        de_reduce = df_merged[df_merged['z_score'] < -3 ]
            
        print(df_increased)
        print(de_reduce)
        
        
if __name__ == "__main__":
    mu = MysqlUtils()
    mu.get_scensic_data()